/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

/**
 *
 * @author Thanh
 */
public class ProductDAL {

    public static void insert(String productId, String productName, String categoryId, float unitPrice, int unitsInStock, int unitsInOrder, String productImage, String description) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement psmt = null;
        String sql = "insert into Products(ProductID, ProductName, CategoryID, UnitPrice,"
                + " UnitsInStock, UnitsInOrder, ProductImage, Description)" + "values(?,?,?,?,?,?,?,?)";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, productId);
            psmt.setString(2, productName);
            psmt.setString(3, categoryId);
            psmt.setFloat(4, unitPrice);
            psmt.setInt(5, unitsInStock);
            psmt.setInt(6, unitsInOrder);
            psmt.setString(7, productImage);
            psmt.setString(8, description);
            psmt.executeUpdate();
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }
    //checkLogin function to check whether the username and password are existed in database

    public static Collection<ProductEntity> searchList(String name) throws ClassNotFoundException, SQLException {
        Collection<ProductEntity> list = null;
        Connection con = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Products where ProductName like '%" + name + "%'";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            rs = psmt.executeQuery();
            list = new ArrayList<ProductEntity>();
            while (rs.next()) {
                ProductEntity entity = new ProductEntity();
                entity.setProductId(rs.getString("ProductID"));
                entity.setProductName(rs.getString("ProductName"));
                entity.setCategoryId(rs.getString("CategoryId"));
                entity.setUnitPrice(rs.getFloat("UnitPrice"));
                entity.setUnitsInStock(rs.getInt("UnitsInStock"));
                entity.setUnitsInOrder(rs.getInt("UnitsInOrder"));
                entity.setProductImage(rs.getString("ProductImage"));
                entity.setDescription(rs.getString("Description"));
                list.add(entity);
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

    //Function to list all employees
    public static Collection<ProductEntity> listAll() throws ClassNotFoundException, SQLException {
        Collection<ProductEntity> list = null;
        Connection con = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from [SMS].[dbo].[Products]";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            rs = psmt.executeQuery();
            list = new ArrayList<ProductEntity>();
            while (rs.next()) {
                ProductEntity entity = new ProductEntity();
                entity.setProductId(rs.getString("ProductID"));
                entity.setProductName(rs.getString("ProductName"));
                entity.setCategoryId(rs.getString("CategoryId"));
                entity.setUnitPrice(rs.getFloat("UnitPrice"));
                entity.setUnitsInStock(rs.getInt("UnitsInStock"));
                entity.setUnitsInOrder(rs.getInt("UnitsInOrder"));
                entity.setProductImage(rs.getString("ProductImage"));
                entity.setDescription(rs.getString("Description"));;
                list.add(entity);
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

    public static Collection<ProductEntity> listByCategoryID(String categoryID) throws ClassNotFoundException, SQLException {
        Collection<ProductEntity> list = null;
        Connection con = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from [SMS].[dbo].[Products] where CategoryId ='" + categoryID + "'";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            rs = psmt.executeQuery();
            list = new ArrayList<ProductEntity>();
            while (rs.next()) {
                ProductEntity entity = new ProductEntity();
                entity.setProductId(rs.getString("ProductID"));
                entity.setProductName(rs.getString("ProductName"));
                entity.setCategoryId(rs.getString("CategoryId"));
                entity.setUnitPrice(rs.getFloat("UnitPrice"));
                entity.setUnitsInStock(rs.getInt("UnitsInStock"));
                entity.setUnitsInOrder(rs.getInt("UnitsInOrder"));
                entity.setProductImage(rs.getString("ProductImage"));
                entity.setDescription(rs.getString("Description"));
                list.add(entity);
            }
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return list;
    }

    public static void modify(String productId, String productName, String categoryId, float unitPrice, int unitsInStock, int unitsInOrder, String productImage, String description) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement psmt = null;
        String sql = "update Products set ProductName=?, CategoryID=?, UnitPrice=?, "
                + "UnitsInStock=?, UnitsInOrder=?, ProductImage=?, Description=? "
                + " where ProductID=?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, productName);
            psmt.setString(2, categoryId);
            psmt.setFloat(3, unitPrice);
            psmt.setInt(4, unitsInStock);
            psmt.setInt(5, unitsInOrder);
            psmt.setString(6, productImage);
            psmt.setString(7, description);
            psmt.setString(8, productId);
            psmt.executeUpdate();
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }

    public static void delete(String productId) throws ClassNotFoundException, SQLException {
        Connection con = null;
        PreparedStatement psmt = null;
        String sql = "delete from Products where ProductID=?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, productId);
            psmt.executeUpdate();
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
    }

    public static ProductEntity searchById(String productId) throws ClassNotFoundException, SQLException {
        Connection con = null;
        ProductEntity entity = new ProductEntity();
        PreparedStatement psmt = null;
        ResultSet rs = null;
        String sql = "select * from Products " + "where ProductID =?";
        try {
            con = ConnectionUtitility.openConnection();
            psmt = con.prepareStatement(sql);
            psmt.setString(1, productId);
            rs = psmt.executeQuery();
            rs.next();
            entity.setProductId(rs.getString("ProductID"));
            entity.setProductName(rs.getString("ProductName"));
            entity.setCategoryId(rs.getString("CategoryId"));
            entity.setUnitPrice(rs.getFloat("UnitPrice"));
            entity.setUnitsInStock(rs.getInt("UnitsInStock"));
            entity.setUnitsInOrder(rs.getInt("UnitsInOrder"));
            entity.setProductImage(rs.getString("ProductImage"));
            entity.setDescription(rs.getString("Description"));
        } finally {
            try {
                psmt.close();
                con.close();
            } catch (Exception e) {
            }
        }
        return entity;
    }
}
